import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

material_control_box_router = APIRouter(prefix="/material/tsControlbox", tags=["管理柜管理"],
                                        dependencies=[Depends(oauth2_scheme)])


class ManagementCabinetInventorySearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("20000101", description="库存开始日期(yyyymmdd)")
    ftakedateEnd: Optional[str] = Field("20000101", description="库存结束日期(yyyymmdd)")
    fcabbarcode: Optional[str] = Field("", description="柜体条码")
    fgoodname: Optional[str] = Field("", description="物料名称(支持前缀匹配)")
    fdicttype: Optional[str] = Field("", description="字典类型编码")
    foperator: Optional[str] = Field("", description="操作人ID")


@material_control_box_router.get("/list", summary="")
def list_material_controlbox(search: ManagementCabinetInventorySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_warehouse_dtl a"
    sql = sql + " left join s_ts_dict b on b.fdicttype=3004 and b.fdictno=cast(fbiztype as varchar)"
    sql = sql + " left join a_ts_site c on c.fsite_no=a.fsite_no"
    sql = sql + " left join am_sys_user d on cast(d.id as varchar)=a.fauthorizer"
    sql = sql + " left join am_sys_user e on cast(e.id as varchar)=a.foperator"
    sql = sql + " left join lims.i_ts_cabinet f on f.fequipmentbarcode=a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse g on f.fequipmentbarcode=a.fcabbarcode"
    sql = sql + " left join i_ts_good_info i on i.fgoodno=a.fgoodno"
    sql = sql + " where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(a.fupddate,'yyyymmddhh24miss') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.fcabbarcode != "":
        sql = sql + " and a.fcabbarcode = :fcabbarcode "
        param["fcabbarcode"] = search.fcabbarcode
    if search.fdicttype != "":
        sql = sql + " and b.fdicttype = :fdicttype "
        param["fdicttype"] = search.fdicttype
    if search.foperator != "":
        sql = sql + " and a.foperator = :foperator "
        param["foperator"] = search.foperator
    if search.fgoodname != "":
        sql = sql + " and i.fgoodname like concat('%', :fgoodname ,'%') "
        param["fgoodname"] = search.fgoodname
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fsite_no,c.fsite_name,f.fpoint_no,a.fupddate,COALESCE(d.username,e.username) fauthorizer"
    sql = sql + ",e.username,a.fcabbarcode,f.fequipmenttag fcabname,a.fboxbarcode,g.fequipmenttag fboxname"
    sql = sql + ",a.fgoodno,i.fgoodname,fbiztype,b.fdictname,a.fqty"
    sql = sql + " from i_warehouse_dtl a"
    sql = sql + " left join s_ts_dict b on b.fdicttype=3004 and b.fdictno=cast(fbiztype as varchar)"
    sql = sql + " left join a_ts_site c on c.fsite_no=a.fsite_no"
    sql = sql + " left join am_sys_user d on cast(d.id as varchar)=a.fauthorizer"
    sql = sql + " left join am_sys_user e on cast(e.id as varchar)=a.foperator"
    sql = sql + " left join lims.i_ts_cabinet f on f.fequipmentbarcode=a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse g on f.fequipmentbarcode=a.fcabbarcode"
    sql = sql + " left join i_ts_good_info i on i.fgoodno=a.fgoodno"
    sql = sql + " where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(a.fupddate,'yyyymmddhh24miss') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.fcabbarcode != "":
        sql = sql + " and a.fcabbarcode = :fcabbarcode "
        param["fcabbarcode"] = search.fcabbarcode
    if search.fdicttype != "":
        sql = sql + " and b.fdicttype = :fdicttype "
        param["fdicttype"] = search.fdicttype
    if search.foperator != "":
        sql = sql + " and a.foperator = :foperator "
        param["foperator"] = search.foperator
    if search.fgoodname != "":
        sql = sql + " and i.fgoodname like concat('%', :fgoodname ,'%') "
        param["fgoodname"] = search.fgoodname
    sql = sql + " order by  a.fupddate "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteNo'] = data[key][0]
        d1['fsiteName'] = data[key][1]
        d1['fpointNo'] = data[key][2]
        d1['fupddate'] = data[key][3]
        d1['fauthorizer'] = data[key][4]
        d1['username'] = data[key][5]
        d1['fcabbarcode'] = data[key][6]
        d1['fcabname'] = data[key][7]
        d1['fboxbarcode'] = data[key][8]
        d1['fboxname'] = data[key][9]
        d1['fgoodno'] = data[key][10]
        d1['fgoodname'] = data[key][11]
        d1['fdicttype'] = data[key][12]
        d1['fdictname'] = data[key][13]
        d1['fqty'] = float(data[key][14])
        d1['ftakedateEnd'] = None
        d1['ftakedateStart'] = None
        data_list.append(d1)
    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
